import sqlite3

class sqliteexecutor():
    _instance = None
    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super(Singleton, cls).__new__(
                                cls, *args, **kwargs)
        return cls._instance
    def __init__(self, source):
        self.conn = sqlite3.connect(source)
        # to refer fields by names then index
        self.conn.row_factory = dict_factory
    def execute(self, args):
        return sqlitequery.parse(args["query"]).execute(self, args["query_args"])
    def executemany(self, args):
        return sqlitequery.parse(args["query"]).executemany(self, args["query_args"])
        
class sqlitequery():
    def __init__(self, query):
        self.query = query
    @staticmethod
    def parse(query):
        return sqlitequery(query)
    def execute(self, instance, args):
        return instance.conn.execute(self.query)
    def executemany(self, instance, args):
        return instance.conn.executemany(self.query, args)

def dict_factory(cursor, row):
    d = {}
    for idx,col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def testrun():
    args = {
            "source_url" : "F:/Code/thinkweb/python/web/queryutils/rdb/sqlite/testsqliteutil.db",
            "query" : '''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''',
            "query_args": {}
           }

    args = {
            "source_url" : "C:/World/Code/thinkweb/thinkweb/trunk/python/web/queryutils/rdb/sqlite/testsqliteutil.db",
            "query" : 'SELECT 1 as ID, "JOHN" as NAME',
            "query_args": {}
           }

    executor = sqliteexecutor("C:/World/Code/thinkweb/thinkweb/trunk/python/web/queryutils/rdb/sqlite/testsqliteutil.db")
    res = executor.execute(args).fetchall()

    print repr(res)
    print res[0]["NAME"]

if __name__ == '__main__':
    print 'running test'
    testrun()
